﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace ClientRegister
{
    public class SubjectInCourse
    {
        ConnectDB con = new ConnectDB();
        SqlConnection cnn;
        SqlCommand cmd;
        SqlDataAdapter ada;
        public int addSubInCourse(string idCourse, string idSub)
        {
            string query = string.Format("insert into SubjectInCourse(IDCourse,IDSub,NewRecord,CreateDateTime) values('{0}','{1}',{2},'{3}')",  idCourse,idSub,1, DateTime.Now.ToString());
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            return cmd.ExecuteNonQuery();
        }

        //select and convert to xml
        public string ConvertSubInCourseToXML(string idCourse, string idSub)
        {
            string query = string.Format("select * from SubjectInCourse where IDCourse = '{0}' and IDSub = '{1}' for xml auto,elements", idCourse, idSub);
            string xmlstring = "";
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);

            xmlstring = cmd.ExecuteScalar().ToString();
            return xmlstring;
        }

        //Lay id cua cot co record = 1
        public DataSet SelectIDNewRecord()
        {
            string query = string.Format("Select IDSub,IDCourse from SubjectInCourse where NewRecord = 1");
            cnn = con.Connect();
            DataSet ds = new DataSet();
            try
            {
                cnn.Open();
                cmd = new SqlCommand(query, cnn);
                ada = new SqlDataAdapter(cmd);
                ada.Fill(ds);
                return ds;
            }
            catch (Exception) { throw; }
            finally
            {
                if (cnn != null && cnn.State == ConnectionState.Open)
                    cnn.Close();
            }
        }
        //Cap nhat lai cot newrecord sau khi dua len server
        public void UpdateNewRecord()
        {
            string query = string.Format("update SubjectInCourse set NewRecord = 0 where NewRecord = 1");
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            cmd.ExecuteNonQuery();
        }

        //Lay id cua cot co record = 2
        public DataSet SelectIDNewRecordUpdate()
        {
            string query = string.Format("Select IDSub,IDCourse from SubjectInCourse where NewRecord = 2");
            cnn = con.Connect();
            DataSet ds = new DataSet();
            try
            {
                cnn.Open();
                cmd = new SqlCommand(query, cnn);
                ada = new SqlDataAdapter(cmd);
                ada.Fill(ds);
                return ds;
            }
            catch (Exception) { throw; }
            finally
            {
                if (cnn != null && cnn.State == ConnectionState.Open)
                    cnn.Close();
            }
        }
        //Cap nhat lai cot newrecord sau khi dua len server
        public void UpdateNewRecordUpdate()
        {
            string query = string.Format("update SubjectInCourse set NewRecord = 0 where NewRecord = 2");
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            cmd.ExecuteNonQuery();
        } 
    }
}
